let express = require('express')
let router = express.Router()

let utils = require('../utils/utils')
let mysqlHelper = require('../utils/mysqlHelper.js')

// 查询
router.get('/', async function(req, res, next) {
    let result = {}
    try {
        let sqlWhere = (req.query.name != '' ? " where name like '%" + req.query.name + "%'" : "")
        result.total = (await mysqlHelper.query("select count(1) as ct from sys_role" + sqlWhere))[0].ct
        let sqlStr = "select a.*,ifnull(b.menuIds,'') as menuIds,ifnull(b.permissionIds,'') as permissionIds from sys_role a "
        sqlStr += "left join (select roleId,group_concat(menuId order by id separator ',') as menuIds,group_concat(permissionId order by id separator ',') as permissionIds FROM sys_role_permissions group by roleId) b "
        sqlStr += "on a.id=b.roleId "
        sqlStr += sqlWhere + " order by convert(name using gbk) limit " + req.query._start + "," + req.query._limit
        result.data = await mysqlHelper.query(sqlStr)
        result.success = true
    } catch (err) {
        result.success = false
        result.err = err.message
    }
    res.json(result)
})

// 表格分页查询 maoyuan
router.get('/list', async function(req, res, next) {
    let result = {}
    try {
        let pageSize = utils.getQueryValue(req.query.pageSize)
        let page = utils.getQueryValue(req.query.page)
        let sqlLimit =  "LIMIT "+(page-1)*pageSize + "," + pageSize
        sqlLimit = ((sqlLimit.indexOf('NaN')||sqlLimit.indexOf("undefined"))?"":sqlLimit)

        let rolename = utils.getQueryValue(req.query.keyword)        
        let sqlWhere = (rolename != '' ? " where (roleName like '%" + rolename + "%' or alias like '%" + rolename + "%')" : "")
        total = (await mysqlHelper.query("select count(1) as ct from sys_role" + sqlWhere))[0].ct
        let sqlStr = "select roleId as id,roleName as label,alias,orderinlist as sort,status,remark,DATE_FORMAT(date,'%Y-%m-%d %H:%i:%s') as date, userIds, userNames from sys_role d   \
                        left join    \
                        (select b.roleId as rid, GROUP_CONCAT(distinct b.userId) userIds, GROUP_CONCAT(distinct a.name) userNames  from sys_user_role b INNER join sys_user a on a.userId=b.userId group by b.roleId) c   \
                        on d.roleId=c.rid"
        sqlStr += sqlWhere + " order by orderinlist " + sqlLimit
        rows = await mysqlHelper.query(sqlStr)
        
        result.code = 200
        result.data = {
            'total': total,
            'page': page,
            'pageSize': pageSize,
            'summary':{
                'id': page*pageSize,
                'name': '999'
            },
            rows: rows
        }
        result.success = true
    } catch (err) {
        result.code = 400
        result.success = false
        result.message = err.message
    }
    res.json(result)
})

// 全部查询 maoyuan
router.get('/listall', async function(req, res, next) {
    let result = {}
    try {
        let keyword = utils.getQueryValue(req.query.keyword)        
        let sqlWhere = " where 1=1"
        sqlWhere += (keyword != '' ? " AND (roleName like '%" + keyword + "%' or alias like '%" + keyword + "%')" : "")
        let sqlStr = "select roleId as id,roleName as label,alias,orderinlist as sort,status,remark,DATE_FORMAT(date,'%Y-%m-%d %H:%i:%s') as date from sys_role"
        sqlStr += sqlWhere + " order by orderinlist " 
        rows = await mysqlHelper.query(sqlStr)
        
        result.code = 200
        result.data = {
            rows: rows
        }
        result.success = true
    } catch (err) {
        result.code = 400
        result.success = false
        result.message = err.message
    }
    res.json(result)
})
// 查询
router.get('/all', async function(req, res, next) {
    let result = {}
    try {
        result.data = await mysqlHelper.query('select * from sys_roles order by convert(name using gbk)')
        result.success = true
    } catch (err) {
        result.success = false
        result.err = err.message
    }
    res.json(result)
})

// 新增修改 maoyuan
router.post('/', async function(req, res, next) {
    let result = {}
    try {
        let id = utils.getQueryValue(req.body.id)
        let userIds = utils.getQueryValue(req.body.userIds)
        let sqlStr =""
        if (id==''){  // 新增
            if ((await mysqlHelper.query("select count(1) as ct from sys_role where roleName='" + req.body.label + "'"))[0].ct > 0) {
                throw new Error('该角色名称已被使用。')
            }
            sqlStr = "insert into sys_role(roleName,alias,orderinlist,remark,status) values('" + req.body.label + "','"+req.body.alias+"',"+req.body.sort+",'"+req.body.remark+"',"+req.body.status+");"
            await mysqlHelper.query(sqlStr)
            sqlStr = ""
            userIds.forEach((item,index)=>{
                sqlStr += "insert into sys_user_role (userId,roleId) select "+item+",roleId from sys_role where roleName='"+req.body.label+"';"
            })
        }
        else{
            sqlStr = "update sys_role set roleName='"+ req.body.label+"',alias='"+ req.body.alias +"',orderinlist="+ req.body.sort+",remark='"+ req.body.remark+"',status="+ req.body.status+",date=now() where roleId="+ id+";"
            sqlStr += "delete from sys_user_role where roleId="+id+";"
            userIds.forEach((item,index)=>{
                sqlStr += "insert into sys_user_role (userId,roleId) values("+item+","+id+");"
            })
        }
        result.code = 200
        result.data = await mysqlHelper.query(sqlStr)
        result.success = true
    } catch (err) {
        result.code = 400
        result.success = false
        result.message = err.message
    }
    res.json(result)
})

// 配置权限 maoyuan
router.put('/permission', async function(req, res, next) {
    let result = {}
    try {
        let permissions = req.body.permissions
        let roleId = req.body.roleId
        let sqlStr = ''
        if (permissions.length > 0) {
            sqlStr = "insert into sys_role_permission(roleId,type,permissionId) values "
            for (let i = 0; i < permissions.length; i++) {
                let id = permissions[i]
                sqlStr += "(" + roleId + ",1,'" + id + "')"
                if (i != permissions.length - 1) {
                    sqlStr += ","
                }
            }
        }
        await mysqlHelper.query('delete from sys_role_permission where roleId=' + roleId)
        if (permissions.length > 0) {
            result.data = await mysqlHelper.query(sqlStr)
        }
        result.code = 200
        result.success = true
    } catch (err) {
        result.code = 400
        result.success = false
        result.message = err.message
    }
    res.json(result)
})

// 删除 maoyuan
router.delete('/', async function(req, res, next) {
    let result = {}
    try {
        var sqlStr = 'delete from sys_role_permission where roleId in (' + req.body.roleIds.join()+'); ';
        sqlStr += 'delete from sys_user_role where roleId in (' + req.body.roleIds.join()+'); '
        sqlStr += 'delete from sys_role where roleId in (' + req.body.roleIds.join()+'); '
        result.data = await mysqlHelper.query(sqlStr)
        result.code = 200
        result.success = true
    } catch (err) {
        result.code = 400
        result.success = false
        result.message = err.message
    }
    res.json(result)
})

module.exports = router
